Excel XLOOKUP 函數

2024-07-09 22:05| 来源: 网络整理| 查看: 265

當我們需要查找資料時，最常使用的是 VLOOKUP(垂直查找) 或 HLOOKUP(水平查找) 函數。自 OFFICE 365 開始， Excel 新增查找函數 XLOOKUP ，其強大的功能可以取代 VLOOKUP 及 HLOOKUP 函數。本文說明 XLOOKUP 函數的複雜操作。另外可以參考基本操作及進階操作。

內容目錄

語法說明

XLOOKUP ( lookup_value, lookup_array, return_array, [ if_not_found ], [ match_mode ], [ search_mode ] )

XLOOKUP ( 查找值, 查找範圍, 回傳值範圍, [ 查照不到的回傳值 ], [ 相符模式 ], [ 查找模式 ] )

備註: [ if_not_found ], [ match_mode ], [ search_mode ] 為選填引數，其餘皆為必填引數

lookup_value 查找值：這是必填的引數，是我們要查找的資料值lookup_array 查找範圍：這是必填的引數，是我們要查找的資料範圍return_array 回傳值範圍：這是必填的引數，是我們要回傳的資料範圍[ if_not_found ] [ 查照不到的回傳值]：這是選填的引數，如果查找不到資料時，會回傳我們指定的值。預設回傳 #N/A 錯誤[ match_mode ] [ 相符模式 ]：這是選填的引數，有下列 4 種模式，預設為 0：0：完全符合，如果查找不到，回傳 #N/A 錯誤-1：完全符合，如果查找不到，回傳下一個較小的值1：完全符合，如果查找不到，回傳下一個較大的值2：使用萬用字元。可參考 Microsoft 說明[ search_mode ] [ 查找模式 ]：這是選填的引數，有下列 4 種模式，預設為 1：1：從第一筆資料開始查找-1：從最後一筆資料開始查找2：二分搜尋法(升序)-2：二分搜尋法(降序) 複雜操作範例

XLOOKUP 函數基本操作範例可以參考 Excel XLOOKUP 函數 | 基本操作說明及範例，進階操作可以參考 Excel XLOOKUP 函數 | 進階操作說明及範例

資料分群

當想要將數值資料分群時，直覺會想到使用 IF 函數。但是當群組級距很多時，IF 函數可能會變得非常複雜。我們可以利用 XLOOKUP 函數的【相符模式】，將數值資料進行分群。不僅執行效率高，函數語法的複雜度也比 IF 函數低，有利於我們閱讀以及維護函數。

語法

= XLOOKUP ( 查找值 , 查找範圍, 回傳範圍, ,-1 )

原理

數值資料分群的原理，是設定 XLOOKUP 函數的【相符模式】，我們可以設定查找小於或是大於的值，回傳分群的對應值

範例

我們在 XLOOKUP 函數的【相符模式】輸入 -1，查找建立好的級距表，就可以分組銷售金額

建立級距表【相符模式】輸入 -1 xlookup_group_formula

多條件查找

當查找的條件超過一個，需要同時符合多個條件時，只需要調整 XLOOKUP 函數【查找範圍】引數，不用搭配其他函數，就可以達到多條件查找

語法

= XLOOKUP ( 1, (條件1範圍=條件1) * (條件2範圍=條件2) * (條件3…), 回傳值範圍 )

原理

多條件查找的原理，是透過每個條件的回傳結果相乘，如果都符合條件，值等於 1；反之，值等於 0。

符合指定條件時，函數回傳 TRUE，數值等於 1；不符合指定條件時，函數回傳 FALSE，數值等於 0將回傳的每個數值相乘如果每個條件都符合，相乘結果為數值 1；如果其中一個條件不符合，相乘結果為數值 0【查找值] 輸入 1，當【查找範圍】回傳 1，代表所有條件都滿足，也就是查找到我們需要的資料範例

我們現在有 3個條件來查找銷售金額，分別如下：

條件條件儲存格查找範圍銷售人員F2A2:A13地區G2B2:B13銷售水果H2C2:C13

函數如下：

= XLOOKUP (1, (A2:A13=F2) * (B2:B13=G2) * (C2:C13=H2), D2:D13)